package kr.hkit.powerjava.project_cafe;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

public class ProductDAO {

	private Connection connection;

	public ProductDAO(Connection connection) {
		this.connection = connection;
	}

	public void insertItems(Product item) throws SQLException { // 입력

		String sql = "insert into cafe_info values (?,?,?,?,?,?)";
		PreparedStatement pstmt = connection.prepareStatement(sql);

		pstmt.setString(1, item.getProdId());
		pstmt.setString(2, item.getProdName());
		int prodPrice = item.getProdPrice();
		pstmt.setInt(3, prodPrice);
		int prodSales = item.getProdSales();
		pstmt.setInt(4, prodSales);
		pstmt.setInt(5, item.getProdMargin());
		pstmt.setInt(6, prodPrice * prodSales);

		pstmt.executeUpdate();

		DbUtil.close(pstmt);

		/*
		 * String sql = "insert into cafe_info values (?,?,?,?,?,?)"; //랭크
		 * 물음표지우기 PreparedStatement pstmt = connection.prepareStatement(sql);
		 * 
		 * pstmt.setInt(1, 1); //얘는 지우기 !! pstmt.setString(2, item.getProdId());
		 * pstmt.setString(3, item.getProdName()); pstmt.setInt(4,
		 * item.getProdPrice()); pstmt.setInt(5, item.getProdSales());
		 * pstmt.setInt(6, item.getProdMargin());
		 * 
		 * pstmt.executeUpdate();
		 * 
		 * DbUtil.close(pstmt);
		 */

	}

	public Product searchItems(Product item) throws SQLException {

		Product pd = null; // 포커스아웃용

		String sql = "select prodname from cafe_info where prodid=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getProdId());

		ResultSet rs = pstmt.executeQuery();

		if (rs.next()) {
			pd = new Product();
			pd.setProdName(rs.getString(1));
		}

		DbUtil.close(rs);
		DbUtil.close(pstmt);

		return pd;
	}

	// 출력1용 쿼리로 도전
	public ArrayList<Product> getProdList() throws SQLException {

		ArrayList<Product> listName = new ArrayList<>();

		String sql = "select (select count(*)+1 from cafe_info c2 "
				+ "where c1.sellingPrice < c2.sellingPrice) as '순위', "
				+ "prodid as '제품코드', prodname as '제품명', prodprice as '제품단가', "
				+ "prodsales as '판매수량', (sellingPrice-((prodprice * prodsales)/11)) as '공급가액',"
				+ "((sellingPrice/11)) as '부가세액', sellingPrice as '판매금액', "
				+ "prodmargin as '마진율',"
				+ "((sellingPrice-((prodprice * prodsales)/11)) * prodmargin) as '마진액'"
				+ " from cafe_info c1";

		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			Product p = new Product(rs.getInt(1), rs.getString(2),
					rs.getString(3), rs.getInt(4), rs.getInt(5),
					rs.getDouble(6), rs.getDouble(7), rs.getInt(8),
					rs.getInt(9), rs.getDouble(10));
			listName.add(p);
		}

		DbUtil.close(rs);
		DbUtil.close(pstmt);

		//System.out.println(listName);

		return listName;
	}
	
	
	
	//결국 arraylist인가여.... 출력 2용
	public ArrayList<Product> getProdList2() throws SQLException {
		
		ArrayList<Product> prod = new ArrayList<>();

		//String sql = "select prodid, prodname, prodprice, prodsales, prodmargin, sellingPrice from cafe_info order by sellingPrice desc";
		String sql = "select prodid, prodname, prodprice, prodsales, prodmargin, sellingPrice from cafe_info";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		
		//int i = 0;
		while (rs.next()) {
			
			Product p2 = new Product(rs.getString(1), rs.getString(2), 
					rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
					//p2.setRank(++i);
			prod.add(p2);
		} 
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		System.out.println(prod);
 
		return prod; //Product형

	}
	
	
	
	/*
	//출력2용 도전 .. 실패.. 밑의 행 알기용이랑 세트
	public String[][] getProdList2() throws SQLException {
		
		String[][] data = null;
		
		int row = getCafe_InfoRowCount(); //행 갯수
		
		String sql = "select prodid, prodname, prodprice, prodsales, prodmargin, sellingPrice "
				+ "from cafe_info";
		
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		ResultSetMetaData rd = rs.getMetaData(); //메타데이터
		data = new String[row][rd.getColumnCount()];
		
		int rank = 1; // 순위 수정할 것
		
		//int i =0;
		while (rs.next()) {
			double vat = (rs.getInt(6) / 11); // 부가세액
			double supply = (rs.getInt(6) - vat); // 공급가액
			double marginPrice = (supply * rs.getInt(5)); // 마진액
			
			for (int j = 0; j <rd.getColumnCount(); j++) {
				//data[j][0] = rank+"";
				//data[i][j] = rs.getString(j+1);
				data[j][1] = rs.getString(1);
				data[j][2] = rs.getString(2);
				data[j][3] = rs.getInt(3)+"";
				data[j][4] = rs.getInt(4)+""; //판매수량
				data[j][5] = supply+"";
				data[j][6] = (rs.getInt(6)/11)+"";
				data[j][7] = rs.getInt(6)+"";
				data[j][8] = rs.getInt(5)+"";
				data[j][9] = marginPrice+"";
				//System.out.println(data);
			}
			//i++;
		}
		
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return data;
		
	}
	
	
	//출력2 행 알기용
	public int getCafe_InfoRowCount() throws SQLException {
		int result = 0;
		
		String sql = "select count(*) from cafe_info";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()) {
			result = rs.getInt(1);
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return result; //행 갯수 알아냄!! 
		
	}
	
	*/
	

	/*// 출력2 - 수정해야함 이상함
	public Product getProdList2() throws SQLException {

		String sql = "select prodid, prodname, prodprice, prodsales, prodmargin, sellingPrice from cafe_info";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		
		Product p2 = null ;
		
		while (rs.next()) {
			p2 = new Product(rs.getString(1), rs.getString(2), 
					rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
			//추가.....
		} 
		
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		System.out.println(p2);
 
		return p2; //Product형

	}*/
}






/*
 * //패스 public ArrayList<Product> listItems() throws SQLException { //arraylist에
 * 담아서 보여줄거임 ArrayList<Product> list = new ArrayList<>(); //String sql =
 * "select deptno, deptname, floor from department"; //PreparedStatement pstmt =
 * connection.prepareStatement(sql); //ResultSet rs = pstmt.executeQuery();
 * //select는 결과필요하니까 executeQuery씀
 * 
 * 
 * int ranking = 1; int price = Integer.parseInt(pProdPrice.getTf().getText());
 * //제품단가 int sales = Integer.parseInt(pProdSales.getTf().getText()); //판매수량 int
 * margin = Integer.parseInt(pProdMargin.getTf().getText()); //마진율
 * 
 * int sellingPrice = (price * sales); //판매금액 double vat = (sellingPrice/11) ;
 * //부가세액 double supply = (sellingPrice - vat); //공급가액 double marginPrice =
 * (supply * margin) ; //마진액
 * 
 * 
 * Product p = new Product(ranking, pProdId.getTf().getText(),
 * pProdName.getTf().getText(), price, sales, supply, vat, sellingPrice, margin,
 * marginPrice);
 * 
 * JOptionPane.showMessageDialog(this, p);
 * 
 * 
 * 
 * while(rs.next()){ //다음 레코드가 있느냐? 있으면 true list.add(new
 * Department(rs.getInt("deptno"), rs.getString("deptname"),
 * rs.getInt("floor"))); //인덱스든 이름이든 상관없음 }
 * 
 * DbUtil.close(rs); DbUtil.close(pstmt);
 * 
 * return list; }
 */

